from sqlalchemy.exc import SQLAlchemyError
from fastapi import APIRouter, Depends, Query
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from typing import Dict, Any, Optional
from app.db.session import get_db
from app.utils.password import hash_password
from app.schemas.user import addUserRequest, updataUserRequest, UpdateUserStatusRequest, profileResquest
from datetime import datetime
from app.utils.token import get_current_user

router = APIRouter()


@router.get('/profile', summary="查询个人资料")
async def get_profile(
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}
    sql = text("SELECT * FROM profile WHERE user_id = :user_id")
    result = await db.execute(sql, {"user_id": user_id})
    profile = result.fetchone()
    if not profile:
        return {"code": -1, "error": "未找到个人信息"}

    user_profile = {
        "nickname": profile._mapping["nickname"],  # type: ignore
        "bio": profile._mapping["bio"],  # type: ignore
    }

    return {
        "code": 0,
        "data": {
            "profile": user_profile,
        }
    }


@router.put('/profile', summary="修改个人资料")
async def edit_profile(
    profile: profileResquest,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}
    nickname = profile.nickname
    bio = profile.bio

    try:
        now = datetime.now()
        sql = text("""
            UPDATE profile SET nickname = :nickname, bio = :bio, updated_at = :updated_at WHERE user_id = :user_id
        """)
        await db.execute(sql, {
            "nickname": nickname,
            "bio": bio,
            "updated_at": now,
            "user_id": user_id
        })
        await db.commit()
    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {"code": -1, "error": "个人资料修改失败"}
    return {"code": 0, "message": "个人资料修改成功"}


@router.get('/', summary="查询用户列表（支持搜索）")
async def get_users(
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(10, ge=1, le=100, description="每页条数"),
    search_name: Optional[str] = Query(None, description="按邮箱模糊搜索")
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}

    offset = (page - 1) * page_size

    # conditions = "is_deleted = 0"
    conditions = "1=1"
    params: Dict[str, Any] = {}

    if search_name:
        conditions += " AND email LIKE :search"
        params["search"] = f"%{search_name}%"

    count_sql = text(f"SELECT COUNT(*) FROM user WHERE {conditions}")
    total_result = await db.execute(count_sql, params)
    total = total_result.scalar()

    user_sql = text(f"""
        SELECT id, email, role, status, last_login_ip, last_login_at, created_at, updated_at,is_deleted
        FROM user
        WHERE {conditions}
        ORDER BY id ASC
        LIMIT :limit OFFSET :offset
    """)
    params.update({"limit": page_size, "offset": offset})
    result = await db.execute(user_sql, params)
    rows = result.fetchall()
    users = [dict(row._mapping) for row in rows]  # type: ignore

    return {
        "code": 0,
        "data": {
            "total": total,
            "users": users
        }
    }


@router.post('/', summary="添加新用户")
async def add_user(
    user: addUserRequest,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}

    try:
        check_sql = text(
            "SELECT 1 FROM user WHERE email = :email AND is_deleted = 0")
        exists = await db.execute(check_sql, {"email": user.email})
        if exists.scalar():
            return {
                "code": -1,
                "error": "该邮箱已存在"
            }

        now = datetime.now()
        default_password = "Xxx@123456."
        hashed_password = hash_password(default_password)

        insert_sql = text("""
            INSERT INTO user (email, role, password_hash, status, is_deleted, created_at, updated_at, last_login_ip, last_login_at)
            VALUES (:email, :role, :password, 1, 0, :created_at, :updated_at, NULL, NULL)
        """)

        await db.execute(insert_sql, {
            "email": user.email,
            "role": user.role,
            "password": hashed_password,
            "created_at": now,
            "updated_at": now,
        })
        await db.commit()

        get_id_sql = text(
            "SELECT id FROM user WHERE email = :email")
        id_result = await db.execute(get_id_sql, {"email": user.email})
        new_user = id_result.fetchone()
        if new_user:
            new_user_id = new_user[0]
            insert_profile_sql = text("""
                INSERT INTO profile (user_id, nickname, bio, created_at, updated_at)
                VALUES (:user_id, '', '', :created_at, :updated_at)
            """)
            await db.execute(insert_profile_sql, {
                "user_id": new_user_id,
                "created_at": now,
                "updated_at": now
            })
            await db.commit()
    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {
            "code": -1,
            "error": f"添加用户失败"
        }

    return {
        "code": 0,
        "data": {}
    }


@router.put('/{id}', summary="修改用户信息")
async def update_user(
    id: int,
    user: updataUserRequest,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}
    try:
        # 先检查用户是否存在且未删除
        check_user_sql = text(
            "SELECT 1 FROM user WHERE id = :id AND is_deleted = 0"
        )
        user_exists = await db.execute(check_user_sql, {"id": id})
        if not user_exists.scalar():
            return {
                "code": -1,
                "error": "用户不存在"
            }

        # 如果邮箱改了，要判断新邮箱是否被其它用户占用
        if user.email:
            check_email_sql = text(
                "SELECT 1 FROM user WHERE email = :email AND id != :id AND is_deleted = 0"
            )
            email_exists = await db.execute(check_email_sql, {"email": user.email, "id": id})
            if email_exists.scalar():
                return {
                    "code": -1,
                    "error": "该邮箱已被其他用户使用"
                }

        now = datetime.now()
        update_sql = text("""
            UPDATE user
            SET email = :email, role = :role, updated_at = :updated_at
            WHERE id = :id AND is_deleted = 0
        """)
        await db.execute(update_sql, {
            "email": user.email,
            "role": user.role,
            "updated_at": now,
            "id": id
        })
        await db.commit()
    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {
            "code": -1,
            "error": "更新用户失败"
        }

    return {
        "code": 0,
        "message": "用户信息更新成功"
    }


@router.delete('/{id}', summary="删除用户（软删除）")
async def delete_user(
    id: int,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}
    try:
        # 检查用户是否存在且未删除
        check_user_sql = text(
            "SELECT 1 FROM user WHERE id = :id AND is_deleted = 0"
        )
        user_exists = await db.execute(check_user_sql, {"id": id})
        if not user_exists.scalar():
            return {
                "code": -1,
                "error": "用户不存在或已被删除"
            }

        now = datetime.now()
        delete_sql = text("""
            UPDATE user SET is_deleted = 1, updated_at = :updated_at WHERE id = :id
        """)
        await db.execute(delete_sql, {"updated_at": now, "id": id})
        await db.commit()

    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {
            "code": -1,
            "error": "删除用户失败"
        }

    return {
        "code": 0,
        "message": "用户已删除"
    }


@router.put('/{id}/status', summary="更新用户状态")
async def update_user_status(
    id: int,
    status_req: UpdateUserStatusRequest,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}
    try:
        # 先检查用户是否存在且未删除
        check_user_sql = text(
            "SELECT 1 FROM user WHERE id = :id AND is_deleted = 0"
        )
        user_exists = await db.execute(check_user_sql, {"id": id})
        if not user_exists.scalar():
            return {
                "code": -1,
                "error": "用户不存在"
            }

        now = datetime.now()
        update_sql = text("""
            UPDATE user
            SET status = :status, updated_at = :updated_at
            WHERE id = :id AND is_deleted = 0
        """)
        await db.execute(update_sql, {
            "status": status_req.status,
            "updated_at": now,
            "id": id
        })
        await db.commit()
    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {
            "code": -1,
            "error": "更新用户状态失败"
        }

    return {
        "code": 0,
        "message": "用户状态更新成功"
    }


@router.post('/{id}/reset/password', summary="重置密码")
async def reset_password(
    id: int,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    current_role = sub.get('role', None)  # type: ignore
    if current_role != 'super_admin':
        return {"code": -1, "error": "无权限"}
    try:
        # 检查用户是否存在且未删除
        check_user_sql = text(
            "SELECT 1 FROM user WHERE id = :id AND is_deleted = 0")
        user_exists = await db.execute(check_user_sql, {"id": id})
        if not user_exists.scalar():
            return {
                "code": -1,
                "error": "用户不存在或已被删除"
            }

        # 设置默认密码
        default_password = "Xxx@123456."
        hashed_password = hash_password(default_password)
        now = datetime.now()
        update_sql = text("""
            UPDATE user SET password_hash = :password, updated_at = :updated_at WHERE id = :id
        """)
        await db.execute(update_sql, {"password": hashed_password, "updated_at": now, "id": id})
        await db.commit()
    except SQLAlchemyError as e:
        await db.rollback()
        print(str(e))
        return {
            "code": -1,
            "error": "重置密码失败"
        }

    return {
        "code": 0,
        "message": "密码已重置"
    }
